package LogicSide;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import DBside.JDBCConnection;
import DBside.TableItem;
import DBside.Tools;
import DBside.JDBCConnection.Table;
import DBside.Tools.Type;

public class ManageSongPlaylist {
	
	/**
	 * 
	 * @param playlistName
	 * @param s - which represents the active session
	 * @return the playlist Id
	 */
	public static int createPlaylist(String playlistName, Session s) {
		JDBCConnection con = s.getCon();
		int playlistId,userId;
		ResultSet playlistGeneretedKeys;
		userId=s.getUser_id();
		playlistId=getPlaylistId(playlistName,s);
		if(playlistId==-1)
		{
			playlistGeneretedKeys = con.insert(Table.PLAYLIST, new TableItem("name", Type.VARCHAR, playlistName));
			try {
				if (playlistGeneretedKeys.next())
				{
					playlistId = playlistGeneretedKeys.getInt(1);
					TableItem[] tableItem = new TableItem[2];
					tableItem[0] = new TableItem("userId", Type.INT, userId);
					tableItem[1] = new TableItem("playlistId", Type.INT, playlistId);
	
					con.insert(Table.USERVSPLAYLIST, tableItem);
					java.util.Date today = new java.util.Date();
					HistoryModifier.addHistoryItem(s, playlistName, Tools.History.CREATEDPLAYLIST, new java.sql.Date(today.getTime()),-1);
					return playlistId;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}
	
	/**
	 * 
	 * @param playlistName
	 * @param s - which represents the active session
	 * @return true if the operation succeed, else false
	 */
	public static boolean deletePlaylist(String playlistName, Session s) {
		JDBCConnection con = s.getCon();
		boolean flag = false;
		int playlistId;
		ResultSet rs;
		playlistId=getPlaylistId(playlistName,s);	
		TableItem[] tableItem = new TableItem[1];
	
		// remove playlist from user:
		tableItem[0] = new TableItem("playlistId", Type.INT, playlistId);
		flag &= con.delete(tableItem, null, Table.USERVSPLAYLIST);
		
		rs=con.select("playlistId", tableItem, null, null, Table.USERVSPLAYLIST);
		if(Tools.isEmpty(rs))
		{
			// remove all the songs from playlist:
			tableItem[0] = new TableItem("playlistId", Type.INT, playlistId);
			flag = con.delete(tableItem, null, Table.PLAYLISTVSSONG);
			
			// delete playlist
			tableItem[0] = new TableItem("id", Type.INT, playlistId);
			flag &= con.delete(tableItem, null, Table.PLAYLIST);
			
		}
		return flag;
	}
	
	public static int getPlaylistId(String playlistName, Session s) {
		JDBCConnection con = s.getCon();
		int user_id=s.getUser_id();
		ResultSet rs,playlistRs;
		TableItem[] ti={new TableItem("userId",Tools.Type.INT,user_id)};
		rs=con.select("playlistId", ti, null, null, Table.USERVSPLAYLIST);
		try {
			while(rs.next())
			{
				TableItem[] ti2={new TableItem("id",Tools.Type.INT,rs.getInt("playlistId")),
								new TableItem("name",Tools.Type.VARCHAR,playlistName)};
				playlistRs=con.select("id", ti2, Tools.Connector.AND, null, Table.PLAYLIST);
				if(playlistRs.next())
					return playlistRs.getInt("id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
		
	}

	/**
	 * 
	 * @param songId
	 * @param playlistName
	 * @param s - which represents the active session
	 * @return true if the operation succeed(if the song has been added to the playlist), else false
	 */
	public static boolean addSongToPlaylist(int songId, String playlistName, Session s) {
		JDBCConnection con = s.getCon();
		ResultSet rs;
		
		int playlistId = getPlaylistId(playlistName,s);
		if(playlistId!=-1)
		{
			TableItem[] TI = { new TableItem("songId", Type.INT, songId),
					new TableItem("playlistId", Type.INT, playlistId) };
			rs = con.select("songId", TI, Tools.Connector.AND, null,
					Table.PLAYLISTVSSONG);
			try {
				if (!rs.next()) {
					rs = con.insert(Table.PLAYLISTVSSONG, TI);
					return true;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}
	
	/**
	 * 
	 * @param songId
	 * @param playlistName
	 * @param s - which represents the active session
	 * @return true if the operation succeed, else false
	 */
	public static boolean removeSongFromPlaylist(int songId, String playlistName, Session s) {
		JDBCConnection con = s.getCon();
		
		int playlistId = getPlaylistId(playlistName,s);
		if(playlistId!=-1)
		{
			TableItem[] TI = { new TableItem("songId", Type.INT, songId),
					new TableItem("playlistId", Type.INT, playlistId) };
			return con.delete(TI, Tools.Connector.AND, Table.PLAYLISTVSSONG);
		}
		return false;
	}

	/**
	 * 
	 * @param playlistName
	 * @param s - which represents the active session
	 * @return return an arraylist with the songs names from the received playlist
	 */
	public static ArrayList<String> getPlaylistSongs(String playlistName, Session s) {
		JDBCConnection con = s.getCon();
		ArrayList<String> lst = new ArrayList<String>();
		ResultSet rs,songRS;
		int playlistId = getPlaylistId(playlistName,s);
		if(playlistId!=-1)
		{
			TableItem[] TI = {new TableItem("playlistId", Type.INT, playlistId) };
			rs = con.select("songId", TI,null, null,Table.PLAYLISTVSSONG);
			try {
				while(rs.next())
				{
					TableItem[] songTI = {new TableItem("id", Type.INT, rs.getInt("songId")) };
					songRS = con.select("name", songTI,null, null,Table.SONG);
					if(songRS.next())
						lst.add(songRS.getString("name"));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lst;
	}

	/**
	 * 
	 * @param s - which represents the active session
	 * @return return an arraylist with all the playlists of the user
	 */
	public static ArrayList<String> getPlaylists(Session s) {
		JDBCConnection con = s.getCon();
		ArrayList<String> lst = new ArrayList<String>();
		ResultSet rs,playlistRS;
		int userID=s.getUser_id();
		TableItem[] TI = {new TableItem("userId", Type.INT, userID) };
		rs = con.select("playlistId", TI,null, null,Table.USERVSPLAYLIST);
		try {
			while(rs.next())
			{
				TableItem[] songTI = {new TableItem("id", Type.INT, rs.getInt("playlistId")) };
				playlistRS = con.select("name", songTI,null, null,Table.PLAYLIST);
				if(playlistRS.next())
				{
					lst.add(playlistRS.getString("name"));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lst;
	}
}
